/**
 * 
 */
package org.istt.dao.hibernate;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.displaytag.properties.SortOrderEnum;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.DistinctRootEntityResultTransformer;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.type.StandardBasicTypes;
import org.istt.dao.FileDao;
import org.istt.domain.AttachFileDetail;
import org.istt.domain.Category;
import org.istt.domain.Exchange;
import org.istt.domain.File;
import org.istt.domain.FileManagement;
import org.istt.domain.SubCategory;
import org.istt.domain.TimeHoliday;
import org.istt.domain.TimeWeekend;
import org.istt.domain.User;
import org.istt.model.FileView;
import org.istt.web.WebConstants;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.opensymphony.xwork2.ActionContext;

/**
 * @author Huy
 * 
 */
@Repository(value = "fileDao")
public class FileDaoHibernate extends BaseDaoHibernate<File, String> implements
		FileDao {
	
	protected final Log log = LogFactory.getLog(getClass());
	
	@Autowired
	public FileDaoHibernate(SessionFactory sessionFactory) {
		super(sessionFactory);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.istt.dao.FileDao#findByCatId(java.lang.Long)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<File> findByCatId(final Long catId) {

		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				List<File> result = session.createCriteria(File.class)
						.add(Example.create(new File()))
						.createCriteria("category")
						.add(Restrictions.idEq(catId)).list();
				return result;
			}
		});
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.istt.dao.FileDao#findByCatId(java.lang.Long)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Exchange> findExchangeByFileId(final String fileId, final Long userId) {

		return (List<Exchange>) hibernateTemplate
				.execute(new HibernateCallback<Object>() {

					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						List<Exchange> result = session
								.createCriteria(Exchange.class)
								.add(Example.create(new Exchange()))
								.addOrder(Order.desc("date"))
								.createAlias("user", "u")
								.createAlias("file", "f")	
								.add(Restrictions.eq("f.id", fileId))
								//.add(Restrictions.eq("u.id", userId))
								.list();
						return result;
					}
				});
	}	
	/*
	 * (non-Javadoc)
	 * 
	 * @see org.istt.dao.FileDao#find(java.lang.Long, java.util.Date,
	 * java.util.Date)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<File> find(final Long cateId, final Date fromDate,
			final Date toDate) {
		if (fromDate == null && toDate == null) {
			throw new IllegalArgumentException("fromDate and toDate must not be null");
		}
		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				Criteria cri = session
								.createCriteria(File.class);
				
				if (fromDate == null) {
					cri.add(Restrictions.le("inputDate", new DateTime(toDate)));
				} else if (toDate == null) {
					cri.add(Restrictions.ge("inputDate", new DateTime(fromDate)));
				} else {
					cri.add(Restrictions.between("inputDate", new DateTime(fromDate), new DateTime(toDate)));
				}
				
				cri.createCriteria("category").add(Restrictions.idEq(cateId));
				List<File> result = cri.list();
				return result;
			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<List<Object>> findByFileIds(final String[] fileIds) {
		return (List<List<Object>>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				StringBuilder builder = new StringBuilder();
				builder.append("select {f.*}, (select [status] from TBL_PROCESS_STATUS where ID = ( ");
				builder.append("	select PROCESS_STATUS_ID from TBL_WORKFLOW_PROCESS where WORKFLOW_ID = w.ID ");
				builder.append("		and POSITION= (select MAX(POSITION) from TBL_WORKFLOW_PROCESS where WORKFLOW_ID = w.ID) ");
				builder.append(")) as [status] from TBL_FILE f inner join TBL_CATEGORY c on f.CATEGORY_ID = c.ID inner join TBL_WORKFLOW w on c.WORKFLOW_ID = w.ID ");
				builder.append("and f.ID in ( ");
				
				String hsql = "";
				for (int i = 0; i < fileIds.length; i++) {
					hsql += "?, ";
				}
				hsql = hsql.substring(0, hsql.length() - 2) + ")";
				builder.append(hsql);
				
				SQLQuery query = session.createSQLQuery(builder.toString()).addEntity("f", File.class).addScalar("status", Hibernate.STRING);
				for (int i = 0; i < fileIds.length; i++) {
					query.setString(i, fileIds[i]);
				}
				return query.list();
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileDao#getWorkFlowId(java.lang.String)
	 */
	@Override
	public Long getWorkFlowId(final String fileId) {
		return (Long) hibernateTemplate.execute(new HibernateCallback<Object>() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String hsql = "select w.id from file f join f.category c join c.workflow w where f.id = ?";
				
				Query query = session.createQuery(hsql);
				query.setString(0, fileId);
				return query.uniqueResult();
			}
		});
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<File> findTrans(final Long fieldId, final Long cateId, final String fileId, final String sender) {
		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(File.class)
				.setFetchMode("Category", FetchMode.JOIN)
				.setFetchMode("Field", FetchMode.JOIN);
				criteria.createAlias("category", "t1");
				criteria.createAlias("t1.field", "t2");
				criteria.add(Restrictions.isNotNull("dateCompleted"));
				criteria.add(Restrictions.isNull("dateGetResult"));
				;
				
				if (fieldId != null) {
					criteria.add(Restrictions.eq("t2.id", fieldId));
				}
				if (cateId != null) {
					criteria.add(Restrictions.eq("t1.id", cateId));
				}
				
				if (!StringUtils.isEmpty(fileId)) {
					criteria.add(Restrictions.like("id", "%" + fileId + "%"));
				}
				
				if (!StringUtils.isEmpty(sender)) {
					criteria.add(Restrictions.like("submitter", "%" + sender + "%"));
				}			
				return criteria.list();

			}
		});		
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> getFileProcessVoucher(final String fileId) {
		return (List<Object[]>) hibernateTemplate
				.execute(new HibernateCallback<Object>() {

					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						StringBuilder builder = new StringBuilder();
						builder.append(" SELECT ROUND(cast(t1.APPROVED_DATE-t1.RECEIVED_DATE as float), 1) AS SUBTIME, t1.APPROVED_COMMENT, CONVERT(varchar,t1.APPROVED_DATE,103) AS APPROVED_DATE, ");
						builder.append(" t4.FIRST_NAME + ' ' + t4.LAST_NAME AS FULLNAME, t3.POSITION, t5.DESCRIPTION, t3.PROCESS_TIME, t1.APPROVE_STATUS, t1.ID, t4.USERNAME, ");
						builder.append(" CONVERT(varchar,t1.RECEIVED_DATE,20) AS FROM_DATE, CONVERT(varchar,t1.APPROVED_DATE,20) AS TO_DATE ");
						builder.append("FROM TBL_FILE_MANAGEMENT t1 ");
						builder.append("INNER JOIN TBL_FILE t2 ON t1.FILE_ID=t2.ID   ");
						builder.append("INNER JOIN TBL_WORKFLOW_PROCESS t3 ON t1.WORKFLOW_PROCESS_ID=t3.ID   ");
						builder.append("INNER JOIN TBL_USER t4 ON t1.APPROVER_ID=t4.ID ");
						builder.append("INNER JOIN TBL_PROCESS_STATUS t5 ON t3.PROCESS_STATUS_ID=t5.ID ");
						//builder.append("WHERE t1.ACTIVE_STATUS = 'true' ");
						if (fileId != null) {
							builder.append(" AND t2.ID = :fileId ");
						}
						builder.append("ORDER BY t3.POSITION ");

						SQLQuery sql = session
								.createSQLQuery(builder.toString())
								// .addEntity("t1", FileManagement.class)
								.addScalar("SUBTIME", StandardBasicTypes.FLOAT)
								.addScalar("APPROVED_DATE", StandardBasicTypes.STRING)
								.addScalar("APPROVED_COMMENT", StandardBasicTypes.STRING)
								.addScalar("FULLNAME", StandardBasicTypes.STRING)
								.addScalar("POSITION", StandardBasicTypes.INTEGER)
								.addScalar("DESCRIPTION", StandardBasicTypes.STRING)
								.addScalar("PROCESS_TIME", StandardBasicTypes.FLOAT)
								.addScalar("APPROVE_STATUS", StandardBasicTypes.BOOLEAN)
								.addScalar("ID", StandardBasicTypes.LONG)
								.addScalar("USERNAME", StandardBasicTypes.STRING)
								.addScalar("FROM_DATE", StandardBasicTypes.STRING)
								.addScalar("TO_DATE", StandardBasicTypes.STRING);
						// sql.setString("ct", "%" + content + "%");

						if (fileId != null) {
							sql.setString("fileId", fileId);
						}
						return sql.list();
					}
				});
	}
	
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileDao#findByStatus(int)
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<File> findByStatus(final int status, final int firstResult, final int maxResults,
			final SortOrderEnum sortDirection, final String sortCriterion) {
		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				User user_login = (User)ActionContext.getContext().getSession().get(WebConstants.USER_INFO);
				Criteria criteria = session.createCriteria(File.class)
				.setFetchMode("FileManagement", FetchMode.JOIN);
				criteria.createAlias("fileManagements", "t1");
				
				if (sortCriterion != null) {
			           if (sortDirection.equals(SortOrderEnum.ASCENDING)) {
			               criteria.addOrder(Order.asc(sortCriterion));
			           }
			           if (sortDirection.equals(SortOrderEnum.DESCENDING)) {
			               criteria.addOrder(Order.desc(sortCriterion));
			           }
			       }
				criteria.add(Restrictions.eq("t1.activeStatus", false));
				criteria.add(Restrictions.eq("t1.approver", user_login));
				criteria.createCriteria("fileStatus")
				.add(Restrictions.idEq(Long.valueOf(status)));
				
				
				return criteria.setFirstResult(firstResult).setMaxResults(maxResults).list();
			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public int findByStatusRecordsCount(final int status) {
		return (Integer) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Criteria criteria = session.createCriteria(File.class)
						.add(Example.create(new File()))
						.createCriteria("fileStatus")
						.add(Restrictions.idEq(Long.valueOf(status)))
						.setProjection(Projections.rowCount());
				
				
				return ((Long)criteria.uniqueResult()).intValue();
			}
		});
	}

	@Override
	public int getSearchRecordCount(final Integer type, final Long departmentId, final Long fileStatusId, final Long fieldId, final Long cateId, final Date fromDate, final Date toDate, final String fileId, final String sender) {
		
		return (Integer) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Calendar t_date = Calendar.getInstance();
				
				Criteria cri = session.createCriteria(File.class);

				if (fromDate == null && toDate != null) {
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					cri.add(Restrictions.le("inputDate", tDate));
				} else if (toDate == null && fromDate != null) {
					cri.add(Restrictions.ge("inputDate", new DateTime(fromDate)));
				} else if (toDate != null && fromDate != null){
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					cri.add(Restrictions.between("inputDate", new DateTime(fromDate), tDate));
				}
				
				if (!StringUtils.isEmpty(fileId)) {
					cri.add(Restrictions.like("id", fileId));
				}
				
				if (!StringUtils.isEmpty(sender)) {
					cri.add(Restrictions.like("sender", sender));
				}
				
				
				/*if ((cateId != 0) && (fieldId != 0)) {
					cri.createCriteria("category").add(Restrictions.idEq(cateId));
				}*/
				if (fileStatusId != 0) {
					cri.createCriteria("fileStatus").add(Restrictions.idEq(fileStatusId));
				}					
				if ((cateId != 0) && (fieldId != 0)) {
					/*cri.setFetchMode("Category", FetchMode.JOIN);
					cri.setFetchMode("Field", FetchMode.JOIN);
					cri.createAlias("category", "t4");
					cri.createAlias("t4.field", "t5");					
					cri.add(Restrictions.eq("t5.id", fieldId));*/
					cri.createCriteria("category").add(Restrictions.idEq(cateId));
					
				}else if((fieldId != 0)){
					cri.setFetchMode("Category", FetchMode.JOIN);
					cri.setFetchMode("Field", FetchMode.JOIN);
					cri.createAlias("category", "t4");
					cri.createAlias("t4.field", "t5");					
					cri.add(Restrictions.eq("t5.id", fieldId));
				}
				if (type == 1) {//file in progress
					cri.setFetchMode("FileManagement", FetchMode.JOIN);
					cri.createAlias("fileManagements", "t1");
					if (departmentId != 0) {
						cri.setFetchMode("User", FetchMode.JOIN);
						cri.createAlias("t1.approver", "t2");
						cri.setFetchMode("Department", FetchMode.JOIN);
						cri.createAlias("t2.department", "t3");					
						cri.add(Restrictions.eq("t3.id", departmentId));
					}						
					cri.add(Restrictions.eq("t1.activeStatus", false));
				}
				
				if (type == 2) { //file completed
					cri.add(Restrictions.isNotNull("dateCompleted"));
					cri.add(Restrictions.isNull("dateGetResult"));
				}
				if (type == 3) {//file returned
					cri.add(Restrictions.isNotNull("dateCompleted"));
					cri.add(Restrictions.isNotNull("dateGetResult"));
				}				
				return ((Long)cri.setProjection(Projections.rowCount()).uniqueResult()).intValue();
			}
		});
	}

	
	@SuppressWarnings("unchecked")
	@Override
	public List<File> search(final Integer type, final Long departmentId, final Long fileStatusId, final Long fieldId, 
			final Long cateId, final Date fromDate, final Date toDate, final String fileId, final String sender,
			final int firstResult, final int maxResults,
			final SortOrderEnum sortDirection, final String sortCriterion) {
		
		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Map<String, Object> parameters = new HashMap<String, Object>();
				boolean firstClause = true;
				StringBuilder sql = new StringBuilder("select f, " +
						"(select fmgr.approver.firstName || ' ' || fmgr.approver.lastName from fileMgr fmgr where fmgr.approvedDate is null and fmgr.file.id = f.id " +
						") as currentUser " +
						"from file f");
				
				Calendar t_date = Calendar.getInstance();
				if (fromDate == null && toDate != null) {
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.inputDate <= :toDate");
					parameters.put("toDate", tDate);
					firstClause = false;
				} else if (toDate == null && fromDate != null) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.inputDate >= :fromDate");
					parameters.put("fromDate", new DateTime(fromDate));
					firstClause = false;
				} else if (toDate != null && fromDate != null){
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.inputDate between :fromDate and :toDate");
					parameters.put("fromDate", new DateTime(fromDate));
					parameters.put("toDate", tDate);
					firstClause = false;
				}
				
				if ((cateId != 0) && (fieldId != 0)) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.category.id = :cateId");
					parameters.put("cateId", cateId);
					firstClause = false;
				}else if (fieldId != 0) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.category.field.id = :fieldId");
					parameters.put("fieldId", fieldId);
					firstClause = false;
				}
				
				if (fileStatusId != 0) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.fileStatus.id = :fileStatusId");
					parameters.put("fileStatusId", fileStatusId);
					firstClause = false;
				}				
							
				if (!StringUtils.isEmpty(fileId)) {
					sql.append(firstClause ? " where " : " and ");
					firstClause = false;
					parameters.put("fileId", "%" + fileId + "%");
					sql.append("f.id like :fileId");
				}
				
				if (!StringUtils.isEmpty(sender)) {
					sql.append(firstClause ? " where " : " and ");
					firstClause = false;
					parameters.put("submitter", "%" + sender + "%");
					sql.append("f.submitter like :submitter");
				}
				
				if (sortCriterion != null) {
						boolean isSelectClause = false;
						if (sortCriterion.equals("currentUser")) {
							isSelectClause =  true;
						}
			           if (sortDirection.equals(SortOrderEnum.ASCENDING)) {
			        	   sql.append(" order by " + (isSelectClause ? "" : "f.") + sortCriterion + " asc");
			           }
			           if (sortDirection.equals(SortOrderEnum.DESCENDING)) {
			        	   sql.append(" order by " + (isSelectClause ? "" : "f.") + sortCriterion + " desc");
			           }
			       } else {
			    	   sql.append(" order by f.inputDate desc");
			       }
				
				Query query = session.createQuery(sql.toString());
				Iterator<String> iter = parameters.keySet().iterator();
				 while (iter.hasNext()) {
					  String name = iter.next();
					  Object value = parameters.get(name);
					  query.setParameter(name,value);
				 }
				 List<Object[]> results = query.setFirstResult(firstResult).setMaxResults(maxResults).list();
				 List<File> files = new ArrayList<File>();
					
					for (int i = 0; i < results.size(); i++) {
						File f = (File)results.get(i)[0];
						f.setCurrentUserFullName((String)results.get(i)[1]);
						files.add(f);
					}
				 
				return files;
			}
		});
	}
	
	@SuppressWarnings("unchecked")
	public List<FileManagement> findFileManagementByFileId(final String fileId, final String username) {
		return (List<FileManagement>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				StringBuilder builder = new StringBuilder();
				builder.append(" SELECT t4.FIRST_NAME + ' ' + t4.LAST_NAME AS FULLNAME, t1.APPROVED_DATE, t3.STATUS, t1.APPROVED_COMMENT, t1.ACTIVE_STATUS, t2.POSITION ");
				builder.append(" FROM TBL_FILE_MANAGEMENT t1 ");
				builder.append("INNER JOIN TBL_WORKFLOW_PROCESS t2 ON t1.WORKFLOW_PROCESS_ID=t2.ID ");
				builder.append("INNER JOIN TBL_PROCESS_STATUS t3 ON t2.PROCESS_STATUS_ID=t3.ID ");
				builder.append("INNER JOIN TBL_USER t4 ON t1.APPROVER_ID=t4.ID ");
				builder.append("WHERE FILE_ID= :fileId AND t4.USERNAME= :username ");
				builder.append("ORDER BY t1.APPROVED_DATE ");
						
				SQLQuery sql = session.createSQLQuery(builder.toString())
				.addScalar("FULLNAME", Hibernate.STRING)
				.addScalar("APPROVED_DATE", Hibernate.DATE)
				.addScalar("STATUS", Hibernate.STRING)
				.addScalar("APPROVED_COMMENT", Hibernate.STRING)
				.addScalar("ACTIVE_STATUS", Hibernate.BOOLEAN)
				.addScalar("POSITION", Hibernate.INTEGER);
				
				if (fileId != null) {
					sql.setString("fileId", fileId);
				}				
				if (username != null) {
					sql.setString("username", username);
				}				
				return sql.list();
			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AttachFileDetail> getAttachFileDetailByFileId(String fileId) {
		String hsql = "from file f join f.attachFileDetails afd where f.id = ?";
		return hibernateTemplate.find(hsql, new String[]{fileId});
	}
	
	@SuppressWarnings("unchecked")
	public List<AttachFileDetail> findAttachFileDetailByFileId(final String fileId) {
		return (List<AttachFileDetail>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(AttachFileDetail.class)
				.setFetchMode("AttachFile", FetchMode.JOIN)
				.setFetchMode("File", FetchMode.JOIN);
				criteria.createAlias("attachFile", "af");
				criteria.createAlias("file", "f");
				
				if (!StringUtils.isEmpty(fileId)) {
					criteria.add(Restrictions.like("f.id", "%" + fileId + "%"));
				}
				
				criteria.addOrder(Order.asc("af.position"));
				return criteria.list();

			}
		});		
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileDao#removeAttachFileDetail(java.lang.Long)
	 */
	@Override
	public void removeAttachFileDetail(String fileId) {
		String hsql = "DELETE AttachFileDetail afd WHERE afd.file.id = ?";
		hibernateTemplate.bulkUpdate(hsql, new String[]{fileId});
	}	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<File> getFilesForReport(final Long cateId,final Long subCateId, final Date fromDate, final Date toDate) {
		if (fromDate == null && toDate == null) {
			throw new IllegalArgumentException("fromDate and toDate must not be null");
		}
		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {
			@SuppressWarnings("deprecation")
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				Criteria cri = session.createCriteria(File.class);
				
				DateTime fDate = new DateTime(fromDate);
				
				//Add time to date to make sure get all files in day.
				Calendar t_date = Calendar.getInstance();
				t_date.setTime(toDate);
				DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
//				cri.add(Expression.or(Restrictions.between("inputDate", fDate, tDate),Expression.and(Restrictions.le("inputDate", fDate), Expression.isNull("dateCompleted"))));
				
				cri.add(Expression.or(Restrictions.between("inputDate", fDate, tDate),
						Expression.or(Expression.and(Restrictions.le("inputDate", fDate), Expression.isNull("dateCompleted")),
								Expression.and(Restrictions.le("inputDate", fDate), Restrictions.between("dateCompleted", fDate, tDate)))));
				
				if(cateId!=null && cateId!=0){
					cri.createCriteria("category").add(Restrictions.idEq(cateId));
				}
				if(subCateId!=null && subCateId!=0){
					cri.createCriteria("subCategory").add(Restrictions.idEq(subCateId));
				}
				
				List<File> result = cri.list();
				return result;
			}
		});
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<File> findByCodeAndName(final String fileCode,final String sender) {
		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Criteria criteria = session.createCriteria(File.class);
				if(fileCode!=null && !"".equals(fileCode)){
					criteria.add(Restrictions.eq("id",fileCode));
				}
				if(sender!=null && !"".equals(sender)){
					criteria.add(Restrictions.like("sender","%" + sender + "%"));
				}
				
				List<File> result = criteria.list();
				List<File> lstFile = new ArrayList<File>();
				for (Iterator iterator = result.iterator(); iterator.hasNext();) {
					File file = (File) iterator.next();
					if(file.getCategory().getIsViewOnnet()){
						lstFile.add(file);
					}
				}
				return lstFile;
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileDao#getFileByUser(java.lang.Long, int, int, org.displaytag.properties.SortOrderEnum, java.lang.String)
	 */
	@Override
	public List<FileManagement> getFileByUser(final Long userId, final Date fromDate, final Date toDate, final int firstRecordIndex,
			final int pageSize, final SortOrderEnum sortDirection, final String sortCriterion) {
		if (userId == null) return null;
		
		return hibernateTemplate.execute(new HibernateCallback<List<FileManagement>>() {
			@Override
			public List<FileManagement> doInHibernate(Session session)
					throws HibernateException, SQLException {
				Map<String, Object> parameters = new HashMap<String, Object>();
				boolean firstClause = true;
				StringBuilder sql = new StringBuilder("select fmgr from fileMgr fmgr join fmgr.file f");
				
				if (userId != null) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("fmgr.approver.id = :userId");
					parameters.put("userId", userId);
					firstClause = false;
				}
				
				Calendar t_date = Calendar.getInstance();
				if (fromDate == null && toDate != null) {
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					
					sql.append(firstClause ? " where " : " and ");
					sql.append("fmgr.approvedDate < :toDate");
					parameters.put("toDate", tDate);
					firstClause = false;
				} else if (toDate == null && fromDate != null) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("fmgr.approvedDate > :fromDate");
					parameters.put("fromDate", new DateTime(fromDate));
					firstClause = false;
				} else if (toDate != null && fromDate != null){
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					
					sql.append(firstClause ? " where " : " and ");
					sql.append("fmgr.approvedDate between :fromDate and :toDate");
					parameters.put("fromDate", new DateTime(fromDate));
					parameters.put("toDate", tDate);
					firstClause = false;
				}
							
				if (sortCriterion != null) {
		           if (sortDirection.equals(SortOrderEnum.ASCENDING)) {
		        	   sql.append(" order by " + sortCriterion + " asc");
		           }
		           if (sortDirection.equals(SortOrderEnum.DESCENDING)) {
		        	   sql.append(" order by " + sortCriterion + " desc");
		           }
		       }
				
				Query query = session.createQuery(sql.toString());
				Iterator<String> iter = parameters.keySet().iterator();
				 while (iter.hasNext()) {
					  String name = iter.next();
					  Object value = parameters.get(name);
					  query.setParameter(name,value);
				 }
				 
				 query.setFirstResult(firstRecordIndex).setMaxResults(pageSize);
				 final ResultTransformer trans = DistinctRootEntityResultTransformer.INSTANCE;
				return trans.transformList(query.list());
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileDao#getFileByUserRecordCount(java.lang.Long)
	 */
	@Override
	public int getFileByUserRecordCount(final Long userId, final Date fromDate, final Date toDate) {
		if (userId == null) return 0;
		return hibernateTemplate.execute(new HibernateCallback<Integer>() {

			@Override
			public Integer doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Criteria criteria = session.createCriteria(FileManagement.class);
				Calendar t_date = Calendar.getInstance();
				
				if (fromDate == null && toDate != null) {
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					
					criteria.add(Restrictions.le("approvedDate", tDate));
				} else if (toDate == null && fromDate != null) {
					criteria.add(Restrictions.ge("approvedDate", new DateTime(fromDate)));
				} else if (toDate != null && fromDate != null){
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					
					criteria.add(Restrictions.between("approvedDate", new DateTime(fromDate), tDate));
				}
				
				criteria.createCriteria("approver")
					.add(Restrictions.idEq(userId))
					.setProjection(Projections.rowCount());
				return ((Long)criteria.uniqueResult()).intValue();
			}
		});
	}

	/***
	 * NDTHO.2012.02.06
	 * @param userId
	 * @param maxResults
	 * @param sortDirection
	 * @param sortCriterion
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<FileView> findFileByApprover(final Long userId,final Date fromDate, final Date toDate, final String status,
			final String filterCondition,final int firstRecordIndex,	final int pageSize, final SortOrderEnum sortDirection, final String sortCriterion) {
		return (List<FileView>) hibernateTemplate.execute(new HibernateCallback<Object>() {
			UtilsDaoHibernate utilsDao = new UtilsDaoHibernate();
			List<TimeWeekend> lstWeekends = getWeekend();
			List<TimeHoliday> lstHolidays = getHoliday();
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Map parameters = new HashMap();
				
				StringBuilder sql = new StringBuilder("SELECT t1.id, t2.receivedDate, t2.approvedDate, t3.processTime, t2.viewStatus, t1.submitter " +
						"FROM file t1, fileMgr t2, WorkflowProcess t3 " +
						"WHERE t1.id=t2.file.id " +
						"AND t2.workflowProcess.id=t3.id ");
				
				if(fromDate!=null && toDate!=null){
					Calendar t_date = Calendar.getInstance();
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					
					sql.append("AND t2.receivedDate between :fromDate and :toDate ");
					parameters.put("fromDate", new DateTime(fromDate));
					parameters.put("toDate", tDate);
				}
				Date today = new Date();
				// (RECEIVE_DATE + PROCESS_TIME) < (TODAY - 1.5)
				if(filterCondition.equals(WebConstants.TASKLIST_FILTER_TYPE_NEW)){
					parameters.put("today", today);
					sql.append("AND DATEADD(dd, t3.processTime + 1.5, t2.receivedDate) < :today ");
				}
				// (TODAY - 1.5) < (RECEIVE_DATE + PROCESS_TIME) < (TODAY - 0.5)
				else if(filterCondition.equals(WebConstants.TASKLIST_FILTER_TYPE_NEARLY_DEADLINE)){
					parameters.put("today", today);
					sql.append("AND DATEADD(dd, t3.processTime + 0.5, t2.receivedDate) < :today ");
//					sql.append("AND DATEADD(dd, t3.processTime + 0.5, t2.receivedDate) > :today ");
				}
				// (RECEIVE_DATE + PROCESS_TIME > TODAY)
				else if(filterCondition.equals(WebConstants.TASKLIST_FILTER_TYPE_OVERDEADLINE)){
					parameters.put("today", today);
					sql.append("AND DATEADD(dd, t3.processTime, t2.receivedDate) > :today ");
				}
				/*FOR TESTING */
				
				//sql.append("AND t3.processTime > (SELECT (DATEDIFF(dd, t2.receivedDate, :today) + 1) - (DATEDIFF(wk, t2.receivedDate, :today) * 2)-(CASE WHEN DATENAME(dw, :today) = 'Sunday' THEN 1 ELSE 0 END) - (CASE WHEN DATENAME(dw, :today) = 'Saturday' THEN 1 ELSE 0 END)) ");
				
				
				sql.append("AND t2.approveStatus = null ");
				sql.append("AND t2.approver.id = :approverId ");
				parameters.put("approverId", userId);
				
				if(status !=null){
					if(status.equals("true")) 
						sql.append("AND t2.activeStatus = 1 ");
					else if(status.equals("false"))
						sql.append("AND t2.activeStatus = 0 ");
				}

				sql.append("ORDER BY t2.receivedDate desc");
				
				Query query = session.createQuery(sql.toString());
				
				Iterator<String> iter = parameters.keySet().iterator();
				 while (iter.hasNext()) {
					  String name = iter.next();
					  Object value = parameters.get(name);
					  query.setParameter(name,value);
				 }
				List<Object[]> results = query.setFirstResult(firstRecordIndex).setMaxResults(pageSize).list();
				List<FileView> files = new ArrayList<FileView>();
					
				for (int i = 0; i < results.size(); i++) {
					FileView f = new FileView();
					f.setId((String)results.get(i)[0]);
					f.setRegulationTime((Float)results.get(i)[3]);
					
					if(results.get(i)[1]!=null)
						f.setReceivedDate(((DateTime)results.get(i)[1]).toDate());
					
					if(results.get(i)[2]!=null)
						f.setApprovedDate(((DateTime)results.get(i)[2]).toDate());
					
					if(results.get(i)[2]!=null){
						float workingday = utilsDao.getWorkingDays((DateTime)results.get(i)[1], (DateTime)results.get(i)[2], lstHolidays, lstWeekends);
						f.setInprogress(((DateTime)results.get(i)[1]).toDate(), ((DateTime)results.get(i)[2]).toDate(), (Float)results.get(i)[3], workingday);
					}else{
						float workingday = utilsDao.getWorkingDays((DateTime)results.get(i)[1], new DateTime(today), lstHolidays, lstWeekends);
						f.setInprogress(((DateTime)results.get(i)[1]).toDate(), null, (Float)results.get(i)[3],workingday);
					}
					
					if(results.get(i)[4]!=null){
						f.setNew((Boolean)results.get(i)[4]);
					}else f.setNew(true);
					
					if(results.get(i)[5]!=null){
						f.setSubmitter((String)results.get(i)[5]);
					}
					
					files.add(f);
				}
				return files;
			}
		});
	}
	
	public List<TimeWeekend> getWeekend() {
		return hibernateTemplate.execute(new HibernateCallback<List<TimeWeekend>>() {

			@SuppressWarnings("unchecked")
			@Override
			public List<TimeWeekend> doInHibernate(Session session)
					throws HibernateException, SQLException {
					
				Criteria criteria = session.createCriteria(TimeWeekend.class);
				
				return criteria.list();
				
			}
		});
	}
	
	public List<TimeHoliday> getHoliday() {
		return hibernateTemplate.execute(new HibernateCallback<List<TimeHoliday>>() {
			
			@SuppressWarnings("unchecked")
			@Override
			public List<TimeHoliday> doInHibernate(Session session)
			throws HibernateException, SQLException {
				
				Criteria criteria = session.createCriteria(TimeHoliday.class);
				
				return criteria.list();
				
			}
		});
	}
	
	/***
	 * Created by NDTHO.2012.02.18
	 * @param userId
	 * @return
	 */
	@Override
	public int checkNewFileByApprover(final Long userId, final Date fromDate, final Date toDate, final boolean status) {
		return hibernateTemplate.execute(new HibernateCallback<Integer>() {

			@Override
			public Integer doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Criteria criteria = session.createCriteria(FileManagement.class);
				
				DateTime fdt = new DateTime(fromDate);
				DateTime from = new DateTime(fdt.getYear(),fdt.getMonthOfYear(), fdt.getDayOfMonth(),00,00);
				DateTime to = new DateTime(toDate);
				
				criteria.add(Restrictions.between("receivedDate", from, to));
				criteria.add(Restrictions.eq("viewStatus", status));
				
				criteria.createCriteria("approver")
					.add(Restrictions.idEq(userId))
					.setProjection(Projections.rowCount());
				return ((Long)criteria.uniqueResult()).intValue();
			}
		});
	}
	
	/*
	 * Created by NDTHO.2012.02.15
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<FileView> findFileByDepartment(final Long departmentId, final String status,
			final int firstRecordIndex, final int pageSize, 
			final SortOrderEnum sortDirection, final String sortCriterion) {
		
		return (List<FileView>) hibernateTemplate.execute(new HibernateCallback<Object>() {
			UtilsDaoHibernate utilsDao = new UtilsDaoHibernate();
			List<TimeWeekend> lstWeekends = getWeekend();
			List<TimeHoliday> lstHolidays = getHoliday();
			@Override
			public Object doInHibernate(Session session)
			throws HibernateException, SQLException {
				StringBuilder sql = new StringBuilder("" +
						"SELECT t1.id, t4.firstName || ' ' || t4.lastName ,t2.receivedDate, t3.processTime, t4.id, t1.submitter " +
						"FROM file t1, fileMgr t2, WorkflowProcess t3, User t4 " +
						"WHERE t1.id=t2.file.id " +
						"AND t2.workflowProcess.id=t3.id " +
						"AND t2.approver.id IN (SELECT t4.id FROM User WHERE t4.department.id = :departmentId) ");
				
				Map parameters = new HashMap();
				parameters.put("departmentId", departmentId);
				if(status !=null && !"".equals(status)){
					if(status.equals("true")) 
						sql.append("AND t2.activeStatus = 1 ");
					else if(status.equals("false"))
						sql.append("AND t2.activeStatus = 0 ");
				}
				
				sql.append("ORDER BY t2.receivedDate desc");
				
				Query query = session.createQuery(sql.toString());
				
				Iterator<String> iter = parameters.keySet().iterator();
				while (iter.hasNext()) {
					String name = iter.next();
					Object value = parameters.get(name);
					query.setParameter(name,value);
				}
				List<Object[]> results = query.setFirstResult(firstRecordIndex).setMaxResults(pageSize).list();
				List<FileView> files = new ArrayList<FileView>();
				
				for (int i = 0; i < results.size(); i++) {
					FileView f = new FileView();
					f.setId((String)results.get(i)[0]);
					f.setApprover((String)results.get(i)[1]);
					if(results.get(i)[2]!=null)
						f.setReceivedDate(((DateTime)results.get(i)[2]).toDate());
					
					f.setRegulationTime((Float)results.get(i)[3]);
					
					//float workingday = utilsDao.getWorkingDays((DateTime)results.get(i)[1], (DateTime)results.get(i)[2], lstHolidays, lstWeekends);
					float workingday = 0;
					
					f.setInprogress(((DateTime)results.get(i)[2]).toDate(), null, (Float)results.get(i)[3],workingday);
					
					f.setApproverId((Long)results.get(i)[4]);
					
					f.setSubmitter((String)results.get(i)[5]);
					
					files.add(f);
				}
				return files;
			}
		});
	}
	
	@Override
	@SuppressWarnings("unchecked")
	public List<File> findFileByDepartmentByDate(final Long deptId, final Date fromDate, final Date toDate) {
		
		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {
			
			@SuppressWarnings("unused")
			@Override
			public Object doInHibernate(Session session)
			throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(File.class);
				log.debug("FROM DATE: " + fromDate.toString());
				log.debug("TO DATE: " + toDate.toString());
				DateTime from = new DateTime(fromDate);
				DateTime to = new DateTime(toDate);
				// Get start point of day (dd/mm/yyyy 00:00)
//				DateTime from = new DateTime(fdt.getYear(),fdt.getMonthOfYear(), fdt.getDayOfMonth(),00,00);
//				
//				DateTime to = new DateTime(tdt.getYear(),tdt.getMonthOfYear(), tdt.getDayOfMonth(),23,59);
				
				Map parameters = new HashMap();
				
				StringBuilder sql = new StringBuilder("SELECT t1.sender, t1.location, t1.inputDate, t1.outputDate, t2.categoryName, t1.address, t1.submitter, t1.number, " +
						"t1.contactName, t1.contactPhone, t1.subCategory.id " +
						"FROM file t1, Category t2, Field t3, Department t4 " +
						"WHERE t1.category = t2.id " +
						"AND t2.field = t3.id " +
						"AND t3.department = t4.id " +
						/*"AND t1.subCategory = t5.id " +*/
						"AND t4.id = :deptid ");
				
				if(from!=null && to!=null){
					sql.append("AND t1.inputDate between :fromDate and :toDate ");
					parameters.put("fromDate", from);
					parameters.put("toDate", to);
				}
				parameters.put("deptid", deptId);

				sql.append("ORDER BY t1.inputDate asc");
				
				Query query = session.createQuery(sql.toString());
				
				Iterator<String> iter = parameters.keySet().iterator();
				 while (iter.hasNext()) {
					  String name = iter.next();
					  Object value = parameters.get(name);
					  query.setParameter(name,value);
				 }
				List<Object[]> results = query.list();
				List<File> files = new ArrayList<File>();
				for (int j = 0; j < results.size(); j++) {
					File f = new File();
					f.setSender((String)results.get(j)[0]);
					f.setLocation((String)results.get(j)[1]);
					f.setInputDate((DateTime)results.get(j)[2]);
					f.setOutputDate((DateTime)results.get(j)[3]);
					
					//Ten thu tuc hanh chinh
					Category cate = new Category();
					cate.setCategoryName((String)results.get(j)[4]);
					f.setCategory(cate);
					
					f.setAddress((String)results.get(j)[5]);
					f.setSubmitter((String)results.get(j)[6]);
					f.setNumber((Integer)results.get(j)[7]);
					f.setContactName((String)results.get(j)[8]);
					f.setContactPhone((String)results.get(j)[9]);
					
					//Ten loai thu tuc hanh chinh
					if(results.get(j)[10] != null){
						SubCategory subCate = new SubCategory();
						subCate.setId((Long)results.get(j)[10]);
						/*subCate = ((SubCategory)results.get(j)[10]);*/
						f.setSubCategory(subCate);
					}
					
					//Them ho so vao danh sach
					files.add(f);
				}
				
				return files;
			}
		});
	}


	/*
	 * Created by NDTHO.2012.02.14
	 * (non-Javadoc)
	 * @see org.istt.dao.FileDao#getRecordCountByFilterCondition(java.lang.Long, java.util.Date, java.util.Date, java.lang.String, java.lang.String)
	 */
	@Override
	public int getRecordCountByFilterCondition(final Long userId,final Date fromDate,final Date toDate,final String status,final String filterCondition) {
		// TODO Auto-generated method stub
		return hibernateTemplate.execute(new HibernateCallback<Integer>() {

			@Override
			public Integer doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Map parameters = new HashMap();
				
				StringBuilder sql = new StringBuilder("SELECT COUNT(*) " +
						"FROM file t1, fileMgr t2, WorkflowProcess t3 " +
						"WHERE t1.id=t2.file.id " +
						"AND t2.workflowProcess.id=t3.id ");
				
				if(fromDate!=null && toDate!=null){
					sql.append("AND t2.receivedDate between :fromDate and :toDate ");
					parameters.put("fromDate", new DateTime(fromDate));
					parameters.put("toDate", new DateTime(toDate));
				}
				
				Date today = new Date();
				if(filterCondition.equals(WebConstants.TASKLIST_FILTER_TYPE_OVERDEADLINE)){
					parameters.put("today", today);
					sql.append("AND DATEADD(dd, t3.processTime, t2.receivedDate) < :today ");
				}
				else if(filterCondition.equals(WebConstants.TASKLIST_FILTER_TYPE_NEARLY_DEADLINE)){
					parameters.put("today", today);
					sql.append("AND DATEADD(dd, t3.processTime, t2.receivedDate) > :today ");
				}
				
				sql.append("AND t2.approveStatus = null ");
				sql.append("AND t2.approver.id = :approverId ");
				parameters.put("approverId", userId);
				
				if(status !=null && !"".equals(status)){
					if(status.equals("true")) 
						sql.append("AND t2.activeStatus = 1 ");
					else if(status.equals("false"))
						sql.append("AND t2.activeStatus = 0 ");
				}

				Query query = session.createQuery(sql.toString());
				
				Iterator<String> iter = parameters.keySet().iterator();
				 while (iter.hasNext()) {
					  String name = iter.next();
					  Object value = parameters.get(name);
					  query.setParameter(name,value);
				 }
				return ((Long)query.uniqueResult()).intValue();
			}
		});
	}

	@Override
	public int getRecordCountByDepartment(final Long departmentId,final String status) {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
		return hibernateTemplate.execute(new HibernateCallback<Integer>() {

			@Override
			public Integer doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				StringBuilder sql = new StringBuilder("" +
						"SELECT COUNT(*) " +
						"FROM file t1, fileMgr t2, WorkflowProcess t3 " +
						"WHERE t1.id=t2.file.id " +
						"AND t2.workflowProcess.id=t3.id " +
						"AND t2.approver.id IN (SELECT t4.id FROM User t4 WHERE t4.department.id = :departmentId) ");
				
				Map parameters = new HashMap();
				parameters.put("departmentId", departmentId);

				if(status !=null && !"".equals(status)){
					if(status.equals("true")) 
						sql.append("AND t2.activeStatus = 1 ");
					else if(status.equals("false"))
						sql.append("AND t2.activeStatus = 0 ");
				}

				Query query = session.createQuery(sql.toString());
				
				Iterator<String> iter = parameters.keySet().iterator();
				 while (iter.hasNext()) {
					  String name = iter.next();
					  Object value = parameters.get(name);
					  query.setParameter(name,value);
				 }
				return ((Long)query.uniqueResult()).intValue();
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileDao#isAbleToNextStep(java.lang.String, java.lang.String)
	 */
	@Override
	public boolean isAbleToNextStep(final String fileId, final String username) {
		return hibernateTemplate.execute(new HibernateCallback<Boolean>() {
		
			@Override
			public Boolean doInHibernate(Session session) throws HibernateException,
					SQLException {
				
				return ((Long)session.createCriteria(FileManagement.class)
									.add(Restrictions.eq("file.id", fileId))
									.add(Restrictions.isNull("approvedDate"))
									.createCriteria("approver")
									.add(Restrictions.eq("username", username))
									.setProjection(Projections.rowCount()).uniqueResult()).intValue() > 0 ? true : false;
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileDao#getMaxNumberNoOfFile()
	 * 
	 */
	@Override
	public int getMaxNumberNoOfFile(final Long fieldId, final int reportStartingPoint) {
		return hibernateTemplate.execute(new HibernateCallback<Integer>() {
			@Override
			public Integer doInHibernate(Session session) throws HibernateException,
					SQLException {
				int queryMonth = DateTime.now().getMonthOfYear();
				int queryYear = DateTime.now().getYear();
				
				if(DateTime.now().getDayOfMonth() < reportStartingPoint){
					// Tinh tu ngay 11 thang truoc toi 11 thang hien tai
					queryMonth = queryMonth - 1;
					
					if(DateTime.now().getMonthOfYear() == 1){
						queryMonth = 12;
						
						queryYear = queryYear - 1;
					}
				}

				DateTime fromDate = new DateTime(queryYear,queryMonth,reportStartingPoint,00,00);
				
				DateTime toDate = new DateTime(DateTime.now().getYear(),DateTime.now().getMonthOfYear(),DateTime.now().getDayOfMonth(),23,00);
				
				Map parameters = new HashMap();
				
				StringBuilder sql = new StringBuilder("SELECT MAX(t1.number) " +
						"FROM file t1, Category t2, Field t3 " +
						"WHERE t1.category.id = t2.id " +
						"AND t2.field.id = t3.id " +
						"AND t3.id =:fieldId " +
						"AND t1.inputDate BETWEEN :fromDate AND :toDate ");
				parameters.put("fromDate", fromDate);
				parameters.put("toDate", toDate);
				parameters.put("fieldId", fieldId);
				
				Query query = session.createQuery(sql.toString());
				
				Iterator<String> iter = parameters.keySet().iterator();
				 while (iter.hasNext()) {
					  String name = iter.next();
					  Object value = parameters.get(name);
					  query.setParameter(name,value);
				 }
				 
				 if(query.uniqueResult()!=null){
					 return ((Integer)query.uniqueResult()).intValue();
				 }else return 0;
//				return ((Integer)query.uniqueResult()).intValue();
			}
		});
	}
	
	/*
	 * (non-Javadoc)
	 * @see org.istt.dao.FileDao#getNumberOfFileOn13th()
	 */
	@Override
	public int getNumberOfFileOnReportDate(final Long fieldId, final int reportStartingPoint) {
		return hibernateTemplate.execute(new HibernateCallback<Integer>() {
			@Override
			public Integer doInHibernate(Session session) throws HibernateException,
					SQLException {
//				Long number = ((Long)session.createCriteria(File.class)
//										.add(Restrictions.sqlRestriction("DAY({alias}.INPUT_DATE) = ?", 13, StandardBasicTypes.INTEGER))
//										.setProjection(Projections.rowCount()).uniqueResult());
//				return number == null ? 0 : number.intValue();

				
				StringBuilder sql = new StringBuilder("" +
						"SELECT COUNT(*) " +
						"FROM file t1, Category t2, Field t3 " +
						"WHERE t1.category.id = t2.id " +
						"AND t2.field.id = t3.id " +
						"AND t3.id =:fieldId " +
						"AND DAY(t1.inputDate) = '"+ reportStartingPoint + "' ");
				
				Map parameters = new HashMap();
				parameters.put("fieldId", fieldId);

				Query query = session.createQuery(sql.toString());
				
				Iterator<String> iter = parameters.keySet().iterator();
				 while (iter.hasNext()) {
					  String name = iter.next();
					  Object value = parameters.get(name);
					  query.setParameter(name,value);
				 }
				return ((Long)query.uniqueResult()).intValue();
			
			}
		});
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<FileManagement> getHistoryByUser(final Long userId,final Date from, final Date to,final int firstRecordIndex, final int pageSize, 
			final SortOrderEnum sortDirection, final String sortCriterion) {
		return (List<FileManagement>) hibernateTemplate.execute(new HibernateCallback<Object>() {

			@SuppressWarnings("unused")
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {

				Calendar t_date = Calendar.getInstance();
				
				t_date.setTime(to);
				
				DateTime toDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
				
				DateTime fromDate = new DateTime(from);
				
				Criteria criteria = session.createCriteria(FileManagement.class);
				criteria.add(Restrictions.eq("sender.id", userId));
				
				criteria.addOrder(Order.desc("receivedDate"));
				
				if (fromDate == null && toDate != null) {
					criteria.add(Restrictions.le("receivedDate", toDate));
				} else if (toDate == null && fromDate != null) {
					criteria.add(Restrictions.ge("receivedDate", fromDate));
				} else if (toDate != null && fromDate != null){
					criteria.add(Restrictions.between("receivedDate", fromDate, toDate));
				}
				
				return criteria.setFirstResult(firstRecordIndex).setMaxResults(pageSize).list();
			}
		});
	}
	
	
	public int getHistoryCountByUser(final Long userId, final Date from, final Date to) {
		return hibernateTemplate.execute(new HibernateCallback<Integer>() {
			@SuppressWarnings("unused")
			@Override
			public Integer doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				Criteria criteria = session.createCriteria(FileManagement.class);
				criteria.add(Restrictions.eq("sender.id", userId));
				
				Calendar t_date = Calendar.getInstance();
				
				t_date.setTime(to);
				
				DateTime toDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
				
				DateTime fromDate = new DateTime(from);
				
				if (fromDate == null && toDate != null) {
					criteria.add(Restrictions.le("receivedDate", toDate));
				} else if (toDate == null && fromDate != null) {
					criteria.add(Restrictions.ge("receivedDate", fromDate));
				} else if (toDate != null && fromDate != null){
					criteria.add(Restrictions.between("receivedDate", fromDate, toDate));
				}
				
				criteria.setProjection(Projections.rowCount());
				
				return ((Long)criteria.uniqueResult()).intValue();
			}
		});
	}

	@Override
	public void remove(String[] ids) {
		if (ArrayUtils.isEmpty(ids)) 
			throw new IllegalArgumentException("ids is null");
		StringBuffer buffer = new StringBuffer();
		for (int i = 0; i < ids.length - 1; i++) {
			buffer.append("? ,");
		}
		buffer.append("? )");
		hibernateTemplate.bulkUpdate("delete from AttachFileDetail a where a.file.id in (" + buffer.toString(), ids);
		hibernateTemplate.bulkUpdate("delete from Exchange e where e.file.id in (" + buffer.toString(), ids);
		hibernateTemplate.bulkUpdate("delete from fileMgr fmgr where fmgr.file.id in (" + buffer.toString(), ids);
		hibernateTemplate.bulkUpdate("delete from file f where f.id in (" + buffer.toString(), ids);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<File> searchWithoutPagination(final  Long departmentId,
			final Long fileStatusId,final  Long fieldId,final  Long cateId,final  Date fromDate,
			final Date toDate,final  String fileId,final  String sender) {
		// TODO Auto-generated method stub
		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Map<String, Object> parameters = new HashMap<String, Object>();
				boolean firstClause = true;
				StringBuilder sql = new StringBuilder("select f, " +
						"(select fmgr.approver.firstName || ' ' || fmgr.approver.lastName from fileMgr fmgr where fmgr.approvedDate is null and fmgr.file.id = f.id " +
						") as currentUser " +
						"from file f");
				
				Calendar t_date = Calendar.getInstance();
				if (fromDate == null && toDate != null) {
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.inputDate <= :toDate");
					parameters.put("toDate", tDate);
					firstClause = false;
				} else if (toDate == null && fromDate != null) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.inputDate >= :fromDate");
					parameters.put("fromDate", new DateTime(fromDate));
					firstClause = false;
				} else if (toDate != null && fromDate != null){
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.inputDate between :fromDate and :toDate");
					parameters.put("fromDate", new DateTime(fromDate));
					parameters.put("toDate", tDate);
					firstClause = false;
				}
				
				if ((cateId != 0) && (fieldId != 0)) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.category.id = :cateId");
					parameters.put("cateId", cateId);
					firstClause = false;
				}else if (fieldId != 0) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.category.field.id = :fieldId");
					parameters.put("fieldId", fieldId);
					firstClause = false;
				}
				
				if (fileStatusId != 0) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.fileStatus.id = :fileStatusId");
					parameters.put("fileStatusId", fileStatusId);
					firstClause = false;
				}				
							
				if (!StringUtils.isEmpty(fileId)) {
					sql.append(firstClause ? " where " : " and ");
					firstClause = false;
					parameters.put("fileId", "%" + fileId + "%");
					sql.append("f.id like :fileId");
				}
				
				if (!StringUtils.isEmpty(sender)) {
					sql.append(firstClause ? " where " : " and ");
					firstClause = false;
					parameters.put("submitter", "%" + sender + "%");
					sql.append("f.submitter like :submitter");
				}
				
				Query query = session.createQuery(sql.toString());
				Iterator<String> iter = parameters.keySet().iterator();
				 while (iter.hasNext()) {
					  String name = iter.next();
					  Object value = parameters.get(name);
					  query.setParameter(name,value);
				 }
				 List<Object[]> results = query.list();
				 List<File> files = new ArrayList<File>();
					
					for (int i = 0; i < results.size(); i++) {
						File f = (File)results.get(i)[0];
						f.setCurrentUserFullName((String)results.get(i)[1]);
						files.add(f);
					}
				 
				return files;
			}
		});
	}

	/*@SuppressWarnings("unchecked")
	@Override
	public List<File> searchFile(final Long cateId,final Date fromDate,final Date toDate,
			final String fileId,final String sender) {
		// TODO Auto-generated method stub
		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Map<String, Object> parameters = new HashMap<String, Object>();
				boolean firstClause = true;
				StringBuilder sql = new StringBuilder("select f.id, f.submitter, f.inputDate, f.outputDate  from file f");
				
				Calendar t_date = Calendar.getInstance();
				if (fromDate == null && toDate != null) {
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.inputDate <= :toDate");
					parameters.put("toDate", tDate);
					firstClause = false;
				} else if (toDate == null && fromDate != null) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.inputDate >= :fromDate");
					parameters.put("fromDate", new DateTime(fromDate));
					firstClause = false;
				} else if (toDate != null && fromDate != null){
					t_date.setTime(toDate);
					DateTime tDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
					
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.inputDate between :fromDate and :toDate");
					parameters.put("fromDate", new DateTime(fromDate));
					parameters.put("toDate", tDate);
					firstClause = false;
				}
				
				if ((cateId != 0)) {
					sql.append(firstClause ? " where " : " and ");
					sql.append("f.category.id = :cateId");
					parameters.put("cateId", cateId);
					firstClause = false;
				}
							
				if (!StringUtils.isEmpty(fileId)) {
					sql.append(firstClause ? " where " : " and ");
					firstClause = false;
					parameters.put("fileId", "%" + fileId + "%");
					sql.append("f.id like :fileId");
				}
				
				if (!StringUtils.isEmpty(sender)) {
					sql.append(firstClause ? " where " : " and ");
					firstClause = false;
					parameters.put("submitter", "%" + sender + "%");
					sql.append("f.submitter like :submitter");
				}
				
				Query query = session.createQuery(sql.toString());
				Iterator<String> iter = parameters.keySet().iterator();
				 while (iter.hasNext()) {
					  String name = iter.next();
					  Object value = parameters.get(name);
					  query.setParameter(name,value);
				 }
				 List<Object[]> results = query.list();
				 List<File> files = new ArrayList<File>();
					
					for (int i = 0; i < results.size(); i++) {
						File f = (File)results.get(i)[0];
//						f.setCurrentUserFullName((String)results.get(i)[1]);
						files.add(f);
					}
				 
				return files;
			}
		});
	}*/
	
	@SuppressWarnings("unchecked")
	@Override
	public List<File> searchFile(final Long cateId,final Date fDate,final Date tDate,
			final String fileCode,final String sender, final int page) {
		return (List<File>) hibernateTemplate.execute(new HibernateCallback<Object>() {
		
			@SuppressWarnings("unused")
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				int recordOnPage = 20;
				Criteria criteria = session.createCriteria(File.class);
				
				Calendar t_date = Calendar.getInstance();
				
				DateTime toDate = null;
				DateTime fromDate = null;
				
				if(tDate!=null){
					t_date.setTime(tDate);
					toDate = new DateTime(t_date.get(Calendar.YEAR),t_date.get(Calendar.MONTH)+1,t_date.get(Calendar.DAY_OF_MONTH),23,59);
				}
				
				if(fDate != null)
					fromDate = new DateTime(fDate);
				
				if (fromDate == null && toDate != null) {
					criteria.add(Restrictions.le("inputDate", toDate));
				} else if (toDate == null && fromDate != null) {
					criteria.add(Restrictions.ge("inputDate", fromDate));
				} else if (toDate != null && fromDate != null){
					criteria.add(Restrictions.between("inputDate", fromDate, toDate));
				}
				
				if(fileCode!=null && !"".equals(fileCode)){
					criteria.add(Restrictions.eq("id",fileCode));
				}
				if(sender!=null && !"".equals(sender)){
					criteria.add(Restrictions.like("sender","%" + sender + "%"));
				}
				if(cateId != 0){
					criteria.add(Restrictions.eq("category.id",cateId));
				}
				
				List<File> result = criteria.setFirstResult(recordOnPage * page).setMaxResults(recordOnPage).list();
				
				return result;
			}
		});
	}

	@Override
	public void removeAllFile() {
		hibernateTemplate.bulkUpdate("delete from AttachFileDetail");
		hibernateTemplate.bulkUpdate("delete from Exchange");
		hibernateTemplate.bulkUpdate("delete from fileMgr");
		hibernateTemplate.bulkUpdate("delete from file");
	}

	@Override
	public void removeAllDataTable() {
		//Remove data related with FILE
		removeAllFile();
		
		//Remove data from Online table
		hibernateTemplate.bulkUpdate("delete from fileAttachment");
		hibernateTemplate.bulkUpdate("delete from fileBoSungCCHN");
		hibernateTemplate.bulkUpdate("delete from fileCapCCHN");
		hibernateTemplate.bulkUpdate("delete from fileCapCCMGBDS");
		hibernateTemplate.bulkUpdate("delete from fileCapLaiCCHN");
		hibernateTemplate.bulkUpdate("delete from fileCapPhepXayDung");
		hibernateTemplate.bulkUpdate("delete from fileDieuChinhGPXD");
		hibernateTemplate.bulkUpdate("delete from fileGiaHanCPXD");
		
		//Remove data from TBL_ATTACH_FILE
		deleteTableContent("TBL_ATTACH_FILE");
		deleteTableContent("TBL_SUB_CATEGORY");
		deleteTableContent("TBL_TIME_HOLIDAY");
		deleteTableContent("TBL_TIME_WEEKEND");
//		deleteTableContent("TBL_POSITION");
		deleteTableContent("TBL_FIELD_TYPE");
		deleteTableContent("TBL_DOCUMENT_TYPE");
		deleteTableContent("TBL_CATEGORY_TYPE");
		deleteTableContent("TBL_WORKFLOW_PROCESS");
		deleteTableContent("TBL_PROCESS_STATUS");
		deleteTableContent("TBL_FILE_STATUS");
		deleteTableContent("TBL_CATEGORY");
		deleteTableContent("TBL_WORKFLOW");
		deleteTableContent("TBL_REPORT");
		
	}
	
	private void deleteTableContent(final String tableName){
		hibernateTemplate.execute(new HibernateCallback<Object>() {

			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				
				StringBuilder builder = new StringBuilder();
				builder.append("DELETE FROM " + tableName);
				
				SQLQuery query = session.createSQLQuery(builder.toString());
				return query.executeUpdate();
			}
		});
	}
	
	
}
